Malloy
The Tao of Malloy
Shape Maps
Segment Maps
The plugin currently supports US maps. Segment maps take as input 4 columns: start latitude , start longitude, end latitude, and end longitude of the segment. The model and data styles for the subsequent examples are:
explore: airports is table('malloy-data.faa.airports') { primary_key: code dimension: name is concat(code, ' - ', full_name) measure: airport_count is count() } explore: flights is table('malloy-data.faa.flights') { primary_key: id2 rename: origin_code is origin rename: destination_code is destination join_one: origin is airports with origin_code join_one: destination is airports with destination_code measure: flight_count is count() query: routes_map is { group_by: [ origin.latitude origin.longitude latitude2 is destination.latitude longitude2 is destination.longitude ] aggregate: flight_count } }
and data styles are
{ "routes_map": { "renderer": "segment_map" } }
Run as a simple query
Departing from Chicago
query: flights { where: dep_time = @2003-02 and origin.code = 'ORD' } -> routes_map
[ { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.88, "longitude2": -93.21, "flight_count": 1006 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.77, "longitude2": -73.87, "flight_count": 834 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 740 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 686 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 684 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 681 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 652 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 646 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 594 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.41, "longitude2": -81.84, "flight_count": 573 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 569 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.36, "longitude2": -71, "flight_count": 550 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 548 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 481 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 480 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 475 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.43, "longitude2": -112, "flight_count": 464 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "flight_count": 458 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.99, "longitude2": -82.89, "flight_count": 425 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 422 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 386 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 370 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 361 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 346 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -78.73, "flight_count": 335 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.29, "longitude2": -94.71, "flight_count": 324 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.53, "longitude2": -93.66, "flight_count": 316 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.06, "longitude2": -73.7, "flight_count": 311 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -77.67, "flight_count": 309 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 304 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.88, "longitude2": -85.52, "flight_count": 304 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 302 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 302 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 302 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.87, "longitude2": -78.78, "flight_count": 300 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -89.97, "flight_count": 299 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 299 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -76.1, "flight_count": 277 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.12, "longitude2": -86.67, "flight_count": 276 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.3, "longitude2": -95.89, "flight_count": 276 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.39, "longitude2": -97.6, "flight_count": 248 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.36, "longitude2": -121.92, "flight_count": 248 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 242 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.88, "longitude2": -91.71, "flight_count": 226 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.19, "longitude2": -76.76, "flight_count": 221 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 220 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 220 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.72, "longitude2": -71.42, "flight_count": 220 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.97, "longitude2": -85.19, "flight_count": 219 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.74, "longitude2": -73.8, "flight_count": 219 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.19, "longitude2": -97.66, "flight_count": 216 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.9, "longitude2": -84.21, "flight_count": 214 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 198 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.89, "longitude2": -76.2, "flight_count": 196 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 27.97, "longitude2": -82.53, "flight_count": 196 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.19, "longitude2": -95.88, "flight_count": 192 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 192 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.53, "longitude2": -98.46, "flight_count": 192 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.13, "longitude2": -89.33, "flight_count": 183 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.66, "longitude2": -89.69, "flight_count": 174 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.5, "longitude2": -77.31, "flight_count": 168 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.81, "longitude2": -83.99, "flight_count": 167 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.89, "longitude2": -82.21, "flight_count": 164 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -87.89, "flight_count": 163 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.64, "longitude2": -70.3, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.53, "longitude2": -84.07, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.64, "longitude2": -97.43, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.48, "longitude2": -88.12, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.5, "longitude2": -74.1, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.03, "longitude2": -88.27, "flight_count": 131 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.03, "longitude2": -87.53, "flight_count": 130 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.58, "longitude2": -83.8, "flight_count": 128 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.47, "longitude2": -73.15, "flight_count": 128 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.43, "longitude2": -66, "flight_count": 124 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.53, "longitude2": -81.75, "flight_count": 114 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.49, "longitude2": -81.68, "flight_count": 113 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.69, "longitude2": -121.59, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.68, "longitude2": -80.09, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.93, "longitude2": -71.43, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.11, "longitude2": -110.94, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.17, "longitude2": -85.73, "flight_count": 111 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.28, "longitude2": -94.3, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.23, "longitude2": -85.55, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.44, "longitude2": -90.5, "flight_count": 100 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.09, "longitude2": -79.93, "flight_count": 100 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.77, "longitude2": -84.58, "flight_count": 96 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.12, "longitude2": -81.2, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 46.91, "longitude2": -96.81, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.47, "longitude2": -88.91, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.8, "longitude2": -104.7, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.32, "longitude2": -79.97, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.33, "longitude2": -75.72, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.37, "longitude2": -81.59, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.72, "longitude2": -122.22, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -80.04, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.91, "longitude2": -81.44, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.03, "longitude2": -84.6, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.93, "longitude2": -81.11, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.9, "longitude2": -92.49, "flight_count": 83 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.87, "longitude2": -91.25, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.4, "longitude2": -90.7, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.24, "longitude2": -93.38, "flight_count": 79 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.65, "longitude2": -75.44, "flight_count": 67 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.82, "longitude2": -116.5, "flight_count": 64 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.03, "longitude2": -85.2, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.49, "longitude2": -119.76, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 31.8, "longitude2": -106.37, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.56, "longitude2": -86.75, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.81, "longitude2": -118.15, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.74, "longitude2": -85.58, "flight_count": 55 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.64, "longitude2": -86.77, "flight_count": 52 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.58, "longitude2": -96.74, "flight_count": 44 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.64, "longitude2": -106.91, "flight_count": 32 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.6, "longitude2": -110.73, "flight_count": 32 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -106.6, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.7, "longitude2": -86.31, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.48, "longitude2": -107.21, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 61.17, "longitude2": -149.99, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.56, "longitude2": -116.22, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.61, "longitude2": -117.53, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.33, "longitude2": -64.97, "flight_count": 8 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 7 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.5, "longitude2": -107.89, "flight_count": 4 } ]
SELECT origin_0.latitude as latitude, origin_0.longitude as longitude, destination_0.latitude as latitude2, destination_0.longitude as longitude2, COUNT( 1) as flight_count FROM `malloy-data.faa.flights` as flights LEFT JOIN `malloy-data.faa.airports` AS destination_0 ON flights.destination = destination_0.code LEFT JOIN `malloy-data.faa.airports` AS origin_0 ON flights.origin = origin_0.code WHERE (((flights.dep_time>='2003-02-01')and(flights.dep_time<'2003-03-01'))and(origin_0.code='ORD')) AND (((flights.dep_time>='2003-02-01')and(flights.dep_time<'2003-03-01'))and(origin_0.code='ORD')) GROUP BY 1,2,3,4 ORDER BY 5 desc
Run as a trellis
By calling the configured map as a nested query, a trellis is formed.
query: flights { where: dep_time = @2003-02 and origin.code = 'ORD' } -> { group_by: carrier aggregate: flight_count nest:routes_map }
| carrier | flight_count | routes_map |
|---|---|---|
| UA | 9,443 | |
| AA | 7,255 | |
| MQ | 4,975 | |
| DH | 3,906 | |
| NW | 702 |
[ { "carrier": "UA", "flight_count": 9443, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 430 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 424 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.77, "longitude2": -73.87, "flight_count": 407 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 388 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.88, "longitude2": -93.21, "flight_count": 388 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 358 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.36, "longitude2": -71, "flight_count": 292 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 287 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 266 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 244 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.99, "longitude2": -82.89, "flight_count": 218 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 212 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 210 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 204 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "flight_count": 194 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 194 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.41, "longitude2": -81.84, "flight_count": 188 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 174 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 170 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.43, "longitude2": -112, "flight_count": 168 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.29, "longitude2": -94.71, "flight_count": 168 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 168 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.3, "longitude2": -95.89, "flight_count": 168 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 168 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 166 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 164 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 162 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.53, "longitude2": -93.66, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 138 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 27.97, "longitude2": -82.53, "flight_count": 116 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 114 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -77.67, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.69, "longitude2": -121.59, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.93, "longitude2": -71.43, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.36, "longitude2": -121.92, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.72, "longitude2": -71.42, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.88, "longitude2": -85.52, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 107 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.53, "longitude2": -81.75, "flight_count": 86 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 86 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.53, "longitude2": -98.46, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.19, "longitude2": -97.66, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -78.73, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.72, "longitude2": -122.22, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.19, "longitude2": -76.76, "flight_count": 68 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.5, "longitude2": -77.31, "flight_count": 68 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.9, "longitude2": -84.21, "flight_count": 68 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.09, "longitude2": -79.93, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.68, "longitude2": -80.09, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.74, "longitude2": -73.8, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.47, "longitude2": -73.15, "flight_count": 40 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.43, "longitude2": -66, "flight_count": 32 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.11, "longitude2": -110.94, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.61, "longitude2": -117.53, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.56, "longitude2": -116.22, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.33, "longitude2": -64.97, "flight_count": 8 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 4 } ] }, { "carrier": "AA", "flight_count": 7255, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 472 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.77, "longitude2": -73.87, "flight_count": 427 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 316 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 312 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 288 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.88, "longitude2": -93.21, "flight_count": 260 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.36, "longitude2": -71, "flight_count": 258 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 235 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 232 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 227 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 186 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 164 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 164 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.43, "longitude2": -112, "flight_count": 160 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.87, "longitude2": -78.78, "flight_count": 160 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 160 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 160 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.29, "longitude2": -94.71, "flight_count": 156 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.06, "longitude2": -73.7, "flight_count": 155 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.36, "longitude2": -121.92, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 133 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.19, "longitude2": -97.66, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.19, "longitude2": -95.88, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.53, "longitude2": -98.46, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.72, "longitude2": -71.42, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 104 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.28, "longitude2": -94.3, "flight_count": 92 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.43, "longitude2": -66, "flight_count": 92 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.11, "longitude2": -110.94, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.9, "longitude2": -92.49, "flight_count": 83 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 27.97, "longitude2": -82.53, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.12, "longitude2": -86.67, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.82, "longitude2": -116.5, "flight_count": 64 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.68, "longitude2": -80.09, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.49, "longitude2": -119.76, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 31.8, "longitude2": -106.37, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.81, "longitude2": -118.15, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 52 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.64, "longitude2": -106.91, "flight_count": 32 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.6, "longitude2": -110.73, "flight_count": 32 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.48, "longitude2": -107.21, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.53, "longitude2": -81.75, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -106.6, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.5, "longitude2": -107.89, "flight_count": 4 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 3 } ] }, { "carrier": "MQ", "flight_count": 4975, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "flight_count": 236 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.41, "longitude2": -81.84, "flight_count": 207 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.99, "longitude2": -82.89, "flight_count": 207 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.13, "longitude2": -89.33, "flight_count": 183 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -77.67, "flight_count": 169 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -78.73, "flight_count": 167 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -87.89, "flight_count": 163 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 159 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.53, "longitude2": -93.66, "flight_count": 152 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -76.1, "flight_count": 137 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.88, "longitude2": -85.52, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.39, "longitude2": -97.6, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.74, "longitude2": -73.8, "flight_count": 135 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.48, "longitude2": -88.12, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.5, "longitude2": -74.1, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.88, "longitude2": -91.71, "flight_count": 132 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.03, "longitude2": -88.27, "flight_count": 131 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.03, "longitude2": -87.53, "flight_count": 130 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.58, "longitude2": -83.8, "flight_count": 128 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 126 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.17, "longitude2": -85.73, "flight_count": 111 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.66, "longitude2": -89.69, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.3, "longitude2": -95.89, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.23, "longitude2": -85.55, "flight_count": 108 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.97, "longitude2": -85.19, "flight_count": 107 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.19, "longitude2": -76.76, "flight_count": 104 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.9, "longitude2": -84.21, "flight_count": 102 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.5, "longitude2": -77.31, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.89, "longitude2": -76.2, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.81, "longitude2": -83.99, "flight_count": 83 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.87, "longitude2": -91.25, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.4, "longitude2": -90.7, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.89, "longitude2": -82.21, "flight_count": 80 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -89.97, "flight_count": 79 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.12, "longitude2": -86.67, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.64, "longitude2": -70.3, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.47, "longitude2": -73.15, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.03, "longitude2": -85.2, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.74, "longitude2": -85.58, "flight_count": 55 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.64, "longitude2": -86.77, "flight_count": 52 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.28, "longitude2": -94.3, "flight_count": 16 } ] }, { "carrier": "DH", "flight_count": 3906, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.06, "longitude2": -73.7, "flight_count": 156 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -76.1, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.12, "longitude2": -86.67, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.53, "longitude2": -84.07, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.87, "longitude2": -78.78, "flight_count": 140 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.64, "longitude2": -97.43, "flight_count": 136 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.49, "longitude2": -81.68, "flight_count": 113 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.89, "longitude2": -76.2, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.39, "longitude2": -97.6, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -89.97, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.97, "longitude2": -85.19, "flight_count": 112 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.44, "longitude2": -90.5, "flight_count": 100 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.77, "longitude2": -84.58, "flight_count": 96 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.88, "longitude2": -91.71, "flight_count": 94 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.47, "longitude2": -88.91, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.64, "longitude2": -70.3, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.19, "longitude2": -95.88, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.32, "longitude2": -79.97, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.93, "longitude2": -81.11, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.91, "longitude2": -81.44, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.81, "longitude2": -83.99, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -78.73, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.12, "longitude2": -81.2, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 46.91, "longitude2": -96.81, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.03, "longitude2": -84.6, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.8, "longitude2": -104.7, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.33, "longitude2": -75.72, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -80.04, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.89, "longitude2": -82.21, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.37, "longitude2": -81.59, "flight_count": 84 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.24, "longitude2": -93.38, "flight_count": 79 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.65, "longitude2": -75.44, "flight_count": 67 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.66, "longitude2": -89.69, "flight_count": 66 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.88, "longitude2": -85.52, "flight_count": 60 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.56, "longitude2": -86.75, "flight_count": 56 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.19, "longitude2": -76.76, "flight_count": 49 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.09, "longitude2": -79.93, "flight_count": 44 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.58, "longitude2": -96.74, "flight_count": 44 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.9, "longitude2": -84.21, "flight_count": 44 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.47, "longitude2": -73.15, "flight_count": 32 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.74, "longitude2": -73.8, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.7, "longitude2": -86.31, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -77.67, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.41, "longitude2": -81.84, "flight_count": 28 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 26 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.53, "longitude2": -93.66, "flight_count": 24 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.5, "longitude2": -77.31, "flight_count": 16 } ] }, { "carrier": "NW", "flight_count": 702, "routes_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.88, "longitude2": -93.21, "flight_count": 358 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 236 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -89.97, "flight_count": 108 } ] } ]
WITH __stage0 AS ( SELECT group_set, flights.carrier as carrier__0, CASE WHEN group_set=0 THEN COUNT( 1) END as flight_count__0, CASE WHEN group_set=1 THEN origin_0.latitude END as latitude__1, CASE WHEN group_set=1 THEN origin_0.longitude END as longitude__1, CASE WHEN group_set=1 THEN destination_0.latitude END as latitude2__1, CASE WHEN group_set=1 THEN destination_0.longitude END as longitude2__1, CASE WHEN group_set=1 THEN COUNT( 1) END as flight_count__1 FROM `malloy-data.faa.flights` as flights LEFT JOIN `malloy-data.faa.airports` AS destination_0 ON flights.destination = destination_0.code LEFT JOIN `malloy-data.faa.airports` AS origin_0 ON flights.origin = origin_0.code CROSS JOIN (SELECT row_number() OVER() -1 group_set FROM UNNEST(GENERATE_ARRAY(0,1,1))) WHERE (((flights.dep_time>='2003-02-01')and(flights.dep_time<'2003-03-01'))and(origin_0.code='ORD')) AND ((group_set NOT IN (1) OR (group_set IN (1) AND ((flights.dep_time>='2003-02-01')and(flights.dep_time<'2003-03-01'))and(origin_0.code='ORD')))) GROUP BY 1,2,4,5,6,7 ) SELECT carrier__0 as carrier, ANY_VALUE(CASE WHEN group_set=0 THEN flight_count__0 END) as flight_count, ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(latitude__1 as latitude, longitude__1 as longitude, latitude2__1 as latitude2, longitude2__1 as longitude2, flight_count__1 as flight_count) END IGNORE NULLS ORDER BY flight_count__1 desc ) as routes_map FROM __stage0 GROUP BY 1 ORDER BY 2 desc
Run as a trellis, repeated with different filters
query: flights -> { group_by: carrier aggregate: flight_count nest: [ ord_segment_map is routes_map { where: origin.code: 'ORD' } sfo_segment_map is routes_map { where: origin.code: 'SFO' } jfk_segment_map is routes_map { where: origin.code: 'JFK' } ] }
| carrier | flight_count | ord_segment_map | sfo_segment_map | jfk_segment_map |
|---|---|---|---|---|
| WN | 5,775,777 | |||
| DL | 4,477,929 | |||
| AA | 4,434,727 | |||
| UA | 3,654,646 | |||
| US | 3,205,810 |
[ { "carrier": "WN", "flight_count": 5775777, "ord_segment_map": [], "sfo_segment_map": [ { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 3673 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.43, "longitude2": -112, "flight_count": 2337 } ], "jfk_segment_map": [] }, { "carrier": "DL", "flight_count": 4477929, "ord_segment_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 24472 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 11819 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 6446 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 269 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 227 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 216 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.05, "longitude2": -117.6, "flight_count": 3 } ], "sfo_segment_map": [ { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 14262 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 8896 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 7184 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 5422 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 4820 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 2191 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 1686 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 42.36, "longitude2": -71, "flight_count": 204 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 29 } ], "jfk_segment_map": [ { "latitude": 40.63, "longitude": -73.77, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 9909 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 8904 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 8733 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 6748 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 5636 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 27.97, "longitude2": -82.53, "flight_count": 5187 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 4836 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 4194 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 26.68, "longitude2": -80.09, "flight_count": 2981 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 2910 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 2655 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.43, "longitude2": -112, "flight_count": 1546 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 1469 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 18.43, "longitude2": -66, "flight_count": 1252 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 1243 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 42.36, "longitude2": -71, "flight_count": 1177 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 1075 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 26.53, "longitude2": -81.75, "flight_count": 957 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 904 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 668 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 604 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 41.97, "longitude2": -87.9, "flight_count": 300 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 294 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 175 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 151 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 120 } ] }, { "carrier": "AA", "flight_count": 4434727, "ord_segment_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 40524 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.77, "longitude2": -73.87, "flight_count": 35579 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 27543 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.36, "longitude2": -71, "flight_count": 24647 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.88, "longitude2": -93.21, "flight_count": 19757 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 19474 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 19394 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 19193 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 19019 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 18792 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 14996 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 14134 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 14063 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.29, "longitude2": -94.71, "flight_count": 13099 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.19, "longitude2": -97.66, "flight_count": 12439 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.87, "longitude2": -78.78, "flight_count": 12128 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 12057 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 11674 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 11603 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.43, "longitude2": -112, "flight_count": 11583 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.36, "longitude2": -121.92, "flight_count": 11043 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 10533 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 10433 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 9707 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 9550 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 9545 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.06, "longitude2": -73.7, "flight_count": 9072 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.19, "longitude2": -95.88, "flight_count": 8971 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 8868 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.72, "longitude2": -71.42, "flight_count": 8511 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.53, "longitude2": -98.46, "flight_count": 8209 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.12, "longitude2": -86.67, "flight_count": 7287 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 7052 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.11, "longitude2": -110.94, "flight_count": 6875 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.43, "longitude2": -66, "flight_count": 6722 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 27.97, "longitude2": -82.53, "flight_count": 6162 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 6045 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.49, "longitude2": -119.76, "flight_count": 5338 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -78.73, "flight_count": 5305 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.9, "longitude2": -92.49, "flight_count": 5262 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 31.8, "longitude2": -106.37, "flight_count": 4989 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -77.67, "flight_count": 4987 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 4682 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -106.6, "flight_count": 4596 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 4445 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "flight_count": 3941 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.28, "longitude2": -94.3, "flight_count": 3621 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.39, "longitude2": -97.6, "flight_count": 3259 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.5, "longitude2": -74.1, "flight_count": 3194 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.74, "longitude2": -73.8, "flight_count": 3192 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.82, "longitude2": -116.5, "flight_count": 3058 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -76.1, "flight_count": 3005 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 2671 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.19, "longitude2": -76.76, "flight_count": 2538 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.68, "longitude2": -80.09, "flight_count": 2068 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.84, "longitude2": -96.85, "flight_count": 1954 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 1694 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.53, "longitude2": -81.75, "flight_count": 1271 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.81, "longitude2": -118.15, "flight_count": 1010 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 20.89, "longitude2": -156.43, "flight_count": 978 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.6, "longitude2": -110.73, "flight_count": 884 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.64, "longitude2": -106.91, "flight_count": 818 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.41, "longitude2": -81.84, "flight_count": 708 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.48, "longitude2": -107.21, "flight_count": 551 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.99, "longitude2": -82.89, "flight_count": 466 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 61.17, "longitude2": -149.99, "flight_count": 441 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.69, "longitude2": -121.59, "flight_count": 286 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.3, "longitude2": -95.89, "flight_count": 175 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.5, "longitude2": -107.89, "flight_count": 141 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 18 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.79, "longitude2": -73.1, "flight_count": 9 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 1 } ], "sfo_segment_map": [ { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 20035 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 15362 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 41.97, "longitude2": -87.9, "flight_count": 14120 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 9865 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 6019 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 42.36, "longitude2": -71, "flight_count": 5367 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 4557 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 4217 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 2280 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.64, "longitude2": -106.91, "flight_count": 327 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 20.89, "longitude2": -156.43, "flight_count": 318 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 38.69, "longitude2": -121.59, "flight_count": 1 } ], "jfk_segment_map": [ { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 21534 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 18.43, "longitude2": -66, "flight_count": 14291 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 11626 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 9876 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 4678 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 4183 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 37.36, "longitude2": -121.92, "flight_count": 3659 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 18.33, "longitude2": -64.97, "flight_count": 2250 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.81, "longitude2": -118.15, "flight_count": 2028 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 2023 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 1744 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 1468 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 42.36, "longitude2": -71, "flight_count": 1183 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 1080 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 37.72, "longitude2": -122.22, "flight_count": 921 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.43, "longitude2": -112, "flight_count": 361 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 214 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 41.97, "longitude2": -87.9, "flight_count": 17 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 3 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 2 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 40.77, "longitude2": -73.87, "flight_count": 2 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 29.64, "longitude2": -95.27, "flight_count": 1 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 29.53, "longitude2": -98.46, "flight_count": 1 } ] }, { "carrier": "UA", "flight_count": 3654646, "ord_segment_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 30763 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.77, "longitude2": -73.87, "flight_count": 30631 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 29875 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 28768 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.88, "longitude2": -93.21, "flight_count": 27161 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 27159 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.36, "longitude2": -71, "flight_count": 24237 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 23663 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 23110 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 19284 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 17647 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 16360 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 14393 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 14376 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.21, "longitude2": -83.34, "flight_count": 14238 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 14153 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 13388 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 13214 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.99, "longitude2": -82.89, "flight_count": 13145 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 12598 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 12469 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.43, "longitude2": -112, "flight_count": 12360 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 12352 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.41, "longitude2": -81.84, "flight_count": 12312 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.71, "longitude2": -86.29, "flight_count": 12307 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.29, "longitude2": -94.71, "flight_count": 12131 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.3, "longitude2": -95.89, "flight_count": 11029 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 10925 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 10329 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.53, "longitude2": -93.66, "flight_count": 10230 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 27.97, "longitude2": -82.53, "flight_count": 10192 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.36, "longitude2": -121.92, "flight_count": 9834 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.69, "longitude2": -121.59, "flight_count": 9451 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 9371 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.72, "longitude2": -71.42, "flight_count": 9213 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.88, "longitude2": -85.52, "flight_count": 8992 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.74, "longitude2": -90.35, "flight_count": 8382 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.93, "longitude2": -71.43, "flight_count": 8265 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -77.67, "flight_count": 8251 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.04, "longitude2": -84.66, "flight_count": 7982 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 7392 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.72, "longitude2": -122.22, "flight_count": 7371 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 7229 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -78.73, "flight_count": 7188 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.9, "longitude2": -84.21, "flight_count": 6661 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.07, "longitude2": -80.15, "flight_count": 6417 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.5, "longitude2": -77.31, "flight_count": 6341 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.19, "longitude2": -76.76, "flight_count": 5798 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 5781 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.74, "longitude2": -73.8, "flight_count": 5460 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 29.53, "longitude2": -98.46, "flight_count": 5453 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.06, "longitude2": -73.7, "flight_count": 5228 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.09, "longitude2": -79.93, "flight_count": 5127 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 41.88, "longitude2": -91.71, "flight_count": 4381 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.53, "longitude2": -81.75, "flight_count": 3969 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 44.47, "longitude2": -73.15, "flight_count": 3659 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.11, "longitude2": -76.1, "flight_count": 3195 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 26.68, "longitude2": -80.09, "flight_count": 2884 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.19, "longitude2": -97.66, "flight_count": 2788 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 37.64, "longitude2": -97.43, "flight_count": 2631 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.87, "longitude2": -78.78, "flight_count": 2607 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.43, "longitude2": -66, "flight_count": 2539 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 47.61, "longitude2": -117.53, "flight_count": 2410 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.56, "longitude2": -116.22, "flight_count": 2398 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.53, "longitude2": -84.07, "flight_count": 2152 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.89, "longitude2": -76.2, "flight_count": 2117 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.8, "longitude2": -104.7, "flight_count": 2036 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.65, "longitude2": -75.44, "flight_count": 1897 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.81, "longitude2": -83.99, "flight_count": 1874 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -89.97, "flight_count": 1833 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.64, "longitude2": -70.3, "flight_count": 1721 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 34.05, "longitude2": -117.6, "flight_count": 1362 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 36.12, "longitude2": -86.67, "flight_count": 1354 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 61.17, "longitude2": -149.99, "flight_count": 1143 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 30.49, "longitude2": -81.68, "flight_count": 1109 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.85, "longitude2": -96.75, "flight_count": 1104 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 42.94, "longitude2": -87.89, "flight_count": 1063 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 988 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 32.11, "longitude2": -110.94, "flight_count": 859 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 18.33, "longitude2": -64.97, "flight_count": 471 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 43.13, "longitude2": -89.33, "flight_count": 457 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.04, "longitude2": -106.6, "flight_count": 431 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 20.89, "longitude2": -156.43, "flight_count": 381 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.17, "longitude2": -85.73, "flight_count": 317 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.56, "longitude2": -86.75, "flight_count": 316 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 33.82, "longitude2": -116.5, "flight_count": 242 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 179 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.64, "longitude2": -106.91, "flight_count": 167 } ], "sfo_segment_map": [ { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 46105 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 41.97, "longitude2": -87.9, "flight_count": 26905 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 23643 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 23029 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.73, "longitude2": -117.18, "flight_count": 21252 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 36.08, "longitude2": -115.15, "flight_count": 18093 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 45.58, "longitude2": -122.59, "flight_count": 17772 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 38.94, "longitude2": -77.45, "flight_count": 16075 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.63, "longitude2": -73.77, "flight_count": 14478 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 34.2, "longitude2": -118.35, "flight_count": 13551 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.67, "longitude2": -117.86, "flight_count": 11412 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 42.36, "longitude2": -71, "flight_count": 11170 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.43, "longitude2": -112, "flight_count": 10905 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.69, "longitude2": -74.16, "flight_count": 8952 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 21.31, "longitude2": -157.92, "flight_count": 8674 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.49, "longitude2": -119.76, "flight_count": 6509 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 5845 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 20.89, "longitude2": -156.43, "flight_count": 5744 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.78, "longitude2": -111.97, "flight_count": 4790 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 44.12, "longitude2": -123.21, "flight_count": 4674 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 34.05, "longitude2": -117.6, "flight_count": 4003 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 3203 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 34.42, "longitude2": -119.84, "flight_count": 3148 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 19.73, "longitude2": -156.04, "flight_count": 3139 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 42.37, "longitude2": -122.87, "flight_count": 3094 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 21.97, "longitude2": -159.33, "flight_count": 2967 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 43.56, "longitude2": -116.22, "flight_count": 2843 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.89, "longitude2": -97.03, "flight_count": 2701 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 33.64, "longitude2": -84.42, "flight_count": 2363 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 28.42, "longitude2": -81.31, "flight_count": 2201 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 29.98, "longitude2": -95.33, "flight_count": 2192 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 29.99, "longitude2": -90.25, "flight_count": 2079 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 1765 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 47.61, "longitude2": -117.53, "flight_count": 1020 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 30.19, "longitude2": -97.66, "flight_count": 905 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 41.93, "longitude2": -72.68, "flight_count": 613 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 61.17, "longitude2": -149.99, "flight_count": 542 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 36.58, "longitude2": -121.84, "flight_count": 340 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 35.04, "longitude2": -106.6, "flight_count": 16 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 32.11, "longitude2": -110.94, "flight_count": 8 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.64, "longitude2": -106.91, "flight_count": 3 } ], "jfk_segment_map": [ { "latitude": 40.63, "longitude": -73.77, "latitude2": 37.61, "longitude2": -122.37, "flight_count": 14472 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 33.94, "longitude2": -118.4, "flight_count": 14370 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 47.44, "longitude2": -122.3, "flight_count": 1298 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 42.36, "longitude2": -71, "flight_count": 805 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 25.79, "longitude2": -80.29, "flight_count": 372 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 18.43, "longitude2": -66, "flight_count": 344 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 41.97, "longitude2": -87.9, "flight_count": 181 }, { "latitude": 40.63, "longitude": -73.77, "latitude2": 39.85, "longitude2": -104.66, "flight_count": 1 } ] }, { "carrier": "US", "flight_count": 3205810, "ord_segment_map": [ { "latitude": 41.97, "longitude": -87.9, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 14576 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 14053 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 11661 }, { "latitude": 41.97, "longitude": -87.9, "latitude2": 38.85, "longitude2": -77.03, "flight_count": 929 } ], "sfo_segment_map": [ { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.87, "longitude2": -75.24, "flight_count": 8532 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 35.21, "longitude2": -80.94, "flight_count": 7192 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 40.49, "longitude2": -80.23, "flight_count": 6553 }, { "latitude": 37.61, "longitude": -122.37, "latitude2": 39.17, "longitude2": -76.66, "flight_count": 1415 } ], "jfk_segment_map": [] } ]
WITH __stage0 AS ( SELECT group_set, flights.carrier as carrier__0, CASE WHEN group_set=0 THEN COUNT( 1) END as flight_count__0, CASE WHEN group_set=1 THEN origin_0.latitude END as latitude__1, CASE WHEN group_set=1 THEN origin_0.longitude END as longitude__1, CASE WHEN group_set=1 THEN destination_0.latitude END as latitude2__1, CASE WHEN group_set=1 THEN destination_0.longitude END as longitude2__1, CASE WHEN group_set=1 THEN COUNT( 1) END as flight_count__1, CASE WHEN group_set=2 THEN origin_0.latitude END as latitude__2, CASE WHEN group_set=2 THEN origin_0.longitude END as longitude__2, CASE WHEN group_set=2 THEN destination_0.latitude END as latitude2__2, CASE WHEN group_set=2 THEN destination_0.longitude END as longitude2__2, CASE WHEN group_set=2 THEN COUNT( 1) END as flight_count__2, CASE WHEN group_set=3 THEN origin_0.latitude END as latitude__3, CASE WHEN group_set=3 THEN origin_0.longitude END as longitude__3, CASE WHEN group_set=3 THEN destination_0.latitude END as latitude2__3, CASE WHEN group_set=3 THEN destination_0.longitude END as longitude2__3, CASE WHEN group_set=3 THEN COUNT( 1) END as flight_count__3 FROM `malloy-data.faa.flights` as flights LEFT JOIN `malloy-data.faa.airports` AS destination_0 ON flights.destination = destination_0.code LEFT JOIN `malloy-data.faa.airports` AS origin_0 ON flights.origin = origin_0.code CROSS JOIN (SELECT row_number() OVER() -1 group_set FROM UNNEST(GENERATE_ARRAY(0,3,1))) WHERE ((group_set NOT IN (1) OR (group_set IN (1) AND origin_0.code='ORD'))) AND ((group_set NOT IN (2) OR (group_set IN (2) AND origin_0.code='SFO'))) AND ((group_set NOT IN (3) OR (group_set IN (3) AND origin_0.code='JFK'))) GROUP BY 1,2,4,5,6,7,9,10,11,12,14,15,16,17 ) SELECT carrier__0 as carrier, ANY_VALUE(CASE WHEN group_set=0 THEN flight_count__0 END) as flight_count, ARRAY_AGG(CASE WHEN group_set=1 THEN STRUCT(latitude__1 as latitude, longitude__1 as longitude, latitude2__1 as latitude2, longitude2__1 as longitude2, flight_count__1 as flight_count) END IGNORE NULLS ORDER BY flight_count__1 desc ) as ord_segment_map, ARRAY_AGG(CASE WHEN group_set=2 THEN STRUCT(latitude__2 as latitude, longitude__2 as longitude, latitude2__2 as latitude2, longitude2__2 as longitude2, flight_count__2 as flight_count) END IGNORE NULLS ORDER BY flight_count__2 desc ) as sfo_segment_map, ARRAY_AGG(CASE WHEN group_set=3 THEN STRUCT(latitude__3 as latitude, longitude__3 as longitude, latitude2__3 as latitude2, longitude2__3 as longitude2, flight_count__3 as flight_count) END IGNORE NULLS ORDER BY flight_count__3 desc ) as jfk_segment_map FROM __stage0 GROUP BY 1 ORDER BY 2 desc